#!/usr/bin/env ruby

require 'open3'

FNAMES = ['Jesse', "James", "Jim", "Jimmy", "Joseph", "Joe", "Michael", "Mike", 
        "Kurt", "Fred", "Matt", "Matthew","Chris", "Chrissy", "Katie", "Kate",
        "Dianne", "Melissa", "Jaxon", "Samarra", "Aiden","Randal", "George", 
        "Kevin", "Jeff"]
LNAMES = ['Brown', "Salvage", "Pinkas", "Day", "Craven", "Walsh", "Schmidt",
        "Gainer", "Gains", "Rollins", "Utley", "Popyack", "Munson", "Smith",
        "Browne", "Michaels", "Jefferson", "Washington", "Lincoln", "Franklin",
        "Burrel", "Jobs", "Gates", "Bloom", "Bloomberg"]
MAJORS = ['Computer Science', 'Basket Arts', 'Electrical Engineering', 'Liberal Arts',
		'Law', 'Business Law', 'Accounting', 'Mathematics', 'Awesomeness']
COURSES = [
    ['CS', 100, 3, "CS Intro to Computing I", "Introduction to computing"],
    ['CS', 101, 2, "CS Intro to Computing II", "Second introduction to computing"],
    ['CS', 150, 1, "CS Intro to Computing III", "Just making sure introduction"],
    ['CS', 200, 3, "CS Programming Java I", "Introduction to java"],
    ['CS', 201, 2, "CS Programming Java II", "Second introduction to java"],
    ['CS', 250, 1, "CS Programming Java III", "Just making sure java"],
    ['CS', 300, 3, "CS Programming C++ I", "Introduction to C++"],
    ['CS', 301, 2, "CS Programming C++ II", "Second introduction to C++"],
    ['CS', 350, 1, "CS Programming C++ III", "Just making sure C++"],
    ['CS', 400, 3, "CS Programming Lisp I", "Introduction to Lisp"],
    ['CS', 401, 2, "CS Programming Lisp II", "Second introduction to Lisp"],
    ['CS', 450, 1, "CS Programming Lisp III", "Just making sure Lisp"],
    ['CS', 500, 4, "CS Programming Haskell I", "Introduction to Haskell"],
    ['CS', 501, 3, "CS Programming Haskell II", "Second introduction to Haskell"],
    ['CS', 550, 2, "CS Programming Haskell III", "Just making sure Haskell"],
    ['CS', 600, 4, "CS Independent Study I", "Introduction to Study"],
    ['CS', 601, 3, "CS Independent Study II", "Second introduction to Study"],
    ['CS', 650, 2, "CS Independent Study III", "Just making sure Study"],
    ['CS', 700, 4, "CS Guided Study I", "Introduction to guided Study"],
    ['CS', 701, 3, "CS Guided Study II", "Second introduction to guided Study"],
    ['CS', 750, 2, "CS Guided Study III", "Just making sure guided Study"],
    ['CS', 800, 4, "CS Structured Study I", "Introduction to structured Study"],
    ['CS', 801, 3, "CS Structured Study II", "Second introduction to structured Study"],
    ['CS', 850, 2, "CS Structured Study III", "Just making sure structured Study"],
    ['CE', 100, 3, "CE Intro to Computing I", "Introduction to computing"],
    ['CE', 101, 2, "CE Intro to Computing II", "Second introduction to computing"],
    ['CE', 150, 1, "CE Intro to Computing III", "Just making sure introduction"],
    ['CE', 200, 3, "CE Programming Java I", "Introduction to java"],
    ['CE', 201, 2, "CE Programming Java II", "Second introduction to java"],
    ['CE', 250, 1, "CE Programming Java III", "Just making sure java"],
    ['CE', 300, 3, "CE Programming C++ I", "Introduction to C++"],
    ['CE', 301, 2, "CE Programming C++ II", "Second introduction to C++"],
    ['CE', 350, 1, "CE Programming C++ III", "Just making sure C++"],
    ['CE', 400, 3, "CE Programming Lisp I", "Introduction to Lisp"],
    ['CE', 401, 2, "CE Programming Lisp II", "Second introduction to Lisp"],
    ['CE', 450, 1, "CE Programming Lisp III", "Just making sure Lisp"],
    ['CE', 500, 4, "CE Programming Haskell I", "Introduction to Haskell"],
    ['CE', 501, 3, "CE Programming Haskell II", "Second introduction to Haskell"],
    ['CE', 550, 2, "CE Programming Haskell III", "Just making sure Haskell"],
    ['CE', 600, 4, "CE Independent Study I", "Introduction to Study"],
    ['CE', 601, 3, "CE Independent Study II", "Second introduction to Study"],
    ['CE', 650, 2, "CE Independent Study III", "Just making sure Study"],
    ['CE', 700, 4, "CE Guided Study I", "Introduction to guided Study"],
    ['CE', 701, 3, "CE Guided Study II", "Second introduction to guided Study"],
    ['CE', 750, 2, "CE Guided Study III", "Just making sure guided Study"],
    ['CE', 800, 4, "CE Structured Study I", "Introduction to structured Study"],
    ['CE', 801, 3, "CE Structured Study II", "Second introduction to structured Study"],
    ['CE', 850, 2, "CE Structured Study III", "Just making sure structured Study"],
    ['BK', 100, 3, "BK Intro to Computing I", "Introduction to computing"],
    ['BK', 101, 2, "BK Intro to Computing II", "Second introduction to computing"],
    ['BK', 150, 1, "BK Intro to Computing III", "Just making sure introduction"],
    ['BK', 200, 3, "BK Programming Java I", "Introduction to java"],
    ['BK', 201, 2, "BK Programming Java II", "Second introduction to java"],
    ['BK', 250, 1, "BK Programming Java III", "Just making sure java"],
    ['BK', 300, 3, "BK Programming C++ I", "Introduction to C++"],
    ['BK', 301, 2, "BK Programming C++ II", "Second introduction to C++"],
    ['BK', 350, 1, "BK Programming C++ III", "Just making sure C++"],
    ['BK', 400, 3, "BK Programming Lisp I", "Introduction to Lisp"],
    ['BK', 401, 2, "BK Programming Lisp II", "Second introduction to Lisp"],
    ['BK', 450, 1, "BK Programming Lisp III", "Just making sure Lisp"],
    ['BK', 500, 4, "BK Programming Haskell I", "Introduction to Haskell"],
    ['BK', 501, 3, "BK Programming Haskell II", "Second introduction to Haskell"],
    ['BK', 550, 2, "BK Programming Haskell III", "Just making sure Haskell"],
    ['BK', 600, 4, "BK Independent Study I", "Introduction to Study"],
    ['BK', 601, 3, "BK Independent Study II", "Second introduction to Study"],
    ['BK', 650, 2, "BK Independent Study III", "Just making sure Study"],
    ['BK', 700, 4, "BK Guided Study I", "Introduction to guided Study"],
    ['BK', 701, 3, "BK Guided Study II", "Second introduction to guided Study"],
    ['BK', 750, 2, "BK Guided Study III", "Just making sure guided Study"],
    ['BK', 800, 4, "BK Structured Study I", "Introduction to structured Study"],
    ['BK', 801, 3, "BK Structured Study II", "Second introduction to structured Study"],
    ['BK', 850, 2, "BK Structured Study III", "Just making sure structured Study"],
    ['HS', 100, 3, "HS Intro to Computing I", "Introduction to computing"],
    ['HS', 101, 2, "HS Intro to Computing II", "Second introduction to computing"],
    ['HS', 150, 1, "HS Intro to Computing III", "Just making sure introduction"],
    ['HS', 200, 3, "HS Programming Java I", "Introduction to java"],
    ['HS', 201, 2, "HS Programming Java II", "Second introduction to java"],
    ['HS', 250, 1, "HS Programming Java III", "Just making sure java"],
    ['HS', 300, 3, "HS Programming C++ I", "Introduction to C++"],
    ['HS', 301, 2, "HS Programming C++ II", "Second introduction to C++"],
    ['HS', 350, 1, "HS Programming C++ III", "Just making sure C++"],
    ['HS', 400, 3, "HS Programming Lisp I", "Introduction to Lisp"],
    ['HS', 401, 2, "HS Programming Lisp II", "Second introduction to Lisp"],
    ['HS', 450, 1, "HS Programming Lisp III", "Just making sure Lisp"],
    ['HS', 500, 4, "HS Programming Haskell I", "Introduction to Haskell"],
    ['HS', 501, 3, "HS Programming Haskell II", "Second introduction to Haskell"],
    ['HS', 550, 2, "HS Programming Haskell III", "Just making sure Haskell"],
    ['HS', 600, 4, "HS Independent Study I", "Introduction to Study"],
    ['HS', 601, 3, "HS Independent Study II", "Second introduction to Study"],
    ['HS', 650, 2, "HS Independent Study III", "Just making sure Study"],
    ['HS', 700, 4, "HS Guided Study I", "Introduction to guided Study"],
    ['HS', 701, 3, "HS Guided Study II", "Second introduction to guided Study"],
    ['HS', 750, 2, "HS Guided Study III", "Just making sure guided Study"],
    ['HS', 800, 4, "HS Structured Study I", "Introduction to structured Study"],
    ['HS', 801, 3, "HS Structured Study II", "Second introduction to structured Study"],
    ['HS', 850, 2, "HS Structured Study III", "Just making sure structured Study"]
]

COURSE_IDS = (1..COURSES.size).to_a
TERMS = [1,2,3,4]
YEARS = [2008,2009,2010,2011,2012]
GRADES = ((0..4).step 0.25).to_a

$students = []
$offerings = []
$schedules = []

def student_table
   cmd = "CREATE TABLE student ("
   cmd << "id SERIAL PRIMARY KEY,"
   cmd << "fname VARCHAR(20) NOT NULL,"
   cmd << "lname VARCHAR(20) NOT NULL,"
   cmd << "major VARCHAR(60) NOT NULL);"
end

def course_table
    cmd = "CREATE TABLE Course ("
    cmd << "id SERIAL PRIMARY KEY,"
    cmd << "dept VARCHAR(5) NOT NULL,"
    cmd << "num INTEGER NOT NULL,"
    cmd << "credits INTEGER NOT NULL,"
    cmd << "name VARCHAR(60) NOT NULL,"
    cmd << "description TEXT);\n"
    cmd << "CREATE UNIQUE INDEX deptnumIDX ON Course (dept, num);"
end

def offering_table
    cmd = "CREATE TABLE Offering ("
    cmd << "id SERIAL PRIMARY KEY,"
    cmd << "dept VARCHAR(5) NOT NULL,"
    cmd << "num INTEGER NOT NULL,"
    cmd << "school_year SMALLINT NOT NULL,"
    cmd << "term SMALLINT NOT NULL,"
    cmd << "FOREIGN KEY (dept, num) REFERENCES Course(dept, num)"
    cmd << "ON DELETE CASCADE);"
end

def schedule_table
    cmd = "CREATE TABLE Schedule ("
    cmd << "student INTEGER NOT NULL,"
    cmd << "course INTEGER NOT NULL,"
    cmd << "grade FLOAT,"
    cmd << "PRIMARY KEY (student, course),"
    cmd << "FOREIGN KEY (student) REFERENCES Student(id)"
    cmd << "ON DELETE CASCADE,"
    cmd << "FOREIGN KEY (course) REFERENCES Offering(id)"
    cmd << "ON DELETE CASCADE);"
end

def drop_all
    cmd = "DROP TABLE schedule;\n"
    cmd << "DROP TABLE offering;\n"
    cmd << "DROP TABLE course;\n"
    cmd << "DROP TABLE student;"
end

def insert_student values
    raise "Student requires 3 values" unless values.size == 3
    puts "INSERT INTO student (fname, lname, major) VALUES ('%s','%s','%s');" % values
end

def insert_course values
    raise "Course requires 5 values" unless values.size == 5
    puts "INSERT INTO course (dept, num, credits, name, description) VALUES ('%s',%d,%d,'%s','%s');" % values
end

def insert_offering values
    raise "Offering requires 4 values" unless values.size == 4
    puts "INSERT INTO offering (dept, num, school_year, term) VALUES ('%s','%s',%d,%d);" % values
end

def insert_schedule values
    raise "Student requires 3 values" unless values.size == 3
    puts "INSERT INTO schedule (student, course, grade) VALUES (%d,%d,%0.2f);" % values
end

def generate_tables 
    puts student_table
    puts course_table
    puts offering_table
    puts schedule_table
end

def clear_database
    puts drop_all
end

def gen_student
	s = [FNAMES.shuffle[0],LNAMES.shuffle[0],MAJORS.shuffle[0]]
	while $students.include? s
		s = [FNAMES.shuffle[0],LNAMES.shuffle[0],MAJORS.shuffle[0]]
	end
	$students << s
	return s
end
	
def gen_course
	COURSES.shuffle[0]
end
	
def gen_offering
	dept,num,_ = COURSES.shuffle[0]
	year = YEARS.shuffle[0]
	term = TERMS.shuffle[0]
	while $offerings.include? [dept,num,year,term]
		dept,num,_ = COURSES.shuffle[0]
		year = YEARS.shuffle[0]
		term = TERMS.shuffle[0]
	end
	$offerings << [dept,num,year,term]
	return [dept,num,year,term]
end

def gen_schedule
	student_id = rand($students.size) + 1
	course_id = rand(COURSES.size) + 1
	grade = GRADES.shuffle[0]
	while $schedules.include? [student_id, course_id]
		student_id = rand($students.size) + 1
		course_id = rand(COURSES.size) + 1
	end
	$schedules << [student_id, course_id]
	return [student_id, course_id, grade]
end

nstudents = 100
ncourses = COURSES.size
nofferings = YEARS.size * (ncourses / 4) # 1/4 of the courses each year)
nschedule = 2 * nstudents

clear_database
generate_tables
nstudents.times { insert_student gen_student }
ncourses.times { |i| insert_course COURSES[i] }
nofferings.times { insert_offering gen_offering }
nschedule.times { insert_schedule gen_schedule }
